﻿using DAL;
using Model;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BLL
{
   public static class SalesManService
    {
        public static List<City> getCity()
        {
            return SqlSugarHelp.scope.Queryable<City>().ToList();
        }
        public static SalesMan getSalesMan(string SalesManCode, string SalesManPass, string SalesManMd5Pass)
        {
            return SqlSugarHelp.scope.Queryable<SalesMan>()
                                        .Where(it => it.SalesManCode == SalesManCode
                                                    && it.SalesManPass == SalesManPass
                                                    && it.SalesManMd5Pass == SalesManMd5Pass)
                                        .First();
        }

        public static List<SalesMan> getAllSalesMan()
        {
            return SqlSugarHelp.scope.Queryable<SalesMan>()
                                    .Mapper(it => it.City,
                                    it => it.SalesManCityID,
                                    it => it.City.id).ToList();
        }

       

        public static int insertSalesMan(SalesMan salesMan)
        {
            return SqlSugarHelp.scope.Insertable(salesMan).ExecuteCommand();
        }

        public static int deleteSalesMan(SalesMan salesMan)
        {
            return SqlSugarHelp.scope.Deleteable<SalesMan>(salesMan).ExecuteCommand();
        }

        public static int updateSalesMan(SalesMan salesMan)
        {
            return SqlSugarHelp.scope.Updateable(salesMan).ExecuteCommand();
        }

        public static List<SalesMan> getSalesManByCityId(double cityId)
        {
            return SqlSugarHelp.scope.Queryable<SalesMan>().Where(it => it.SalesManCityID == cityId).ToList();
        }

        public static List<SalesMan> getAllSalesManWithTotalCustomerPotential()
        {
            //查询所有业务员
            List<SalesMan> salesManList = getAllSalesMan();
                //SqlSugarHelp.scope.Queryable<SalesMan>()
                //                                     .LeftJoin<PotentialSalesman>((sale, potential) => sale.Id == potential.SalesManId)
                //                                     .Where((sale, potential) => potential.Status == 1 || potential.Status == null)
                //                                     .GroupBy((sale, potential) => new { sale.Id, sale.SalesManCode, sale.SalesManName , sale.SalesScore})
                //                                     .OrderBy(sale => sale.SalesScore)
                //                                     .Select((sale, potential) =>
                //                                                                new SalesMan()
                //                                                                {
                //                                                                    Id = sale.Id,
                //                                                                    SalesManCode = sale.SalesManCode,
                //                                                                    SalesManName = sale.SalesManName,
                //                                                                    SalesScore = sale.SalesScore,
                //                                                                    TotalCustomerPotential = SqlFunc.AggregateCount(1)
                //                                                                }).ToList();
            
            if (salesManList != null)
            {
                foreach (var item in salesManList)
                {
                    //查询业务员已经分配的客户数
                    item.TotalCustomerPotential = SqlSugarHelp.scope.Queryable<PotentialSalesman>().Where(it => it.SalesManId == item.Id).Count();
                    //查询这个业务已经分配过，但是已经过了保护期没能成交的客户deadcustomerMainList（status 为 0）
                    item.deadcustomerMainList = SqlSugarHelp.scope.Queryable<PotentialSalesman>()
                                                            .LeftJoin<CustomerPotential>((potsale, cuspot) => potsale.PotentialId == cuspot.Id)
                                                            .LeftJoin<CustomerMain>((potsale, cuspot, cusmain) => cuspot.CustomerMainId == cusmain.Id)
                                                            .Where(potsale => potsale.SalesManId == item.Id && potsale.Status == 0 )
                                                            .Select<CustomerMain>()
                                                            .ToList();  //ViewOrder是一个新建的类，更多Select用法看下面文档 
                }
            }
            return salesManList;
        }

        public static List<OrganizationManager> getOraManager(int orgId)
        {
            return SqlSugarHelp.scope.Queryable<OrganizationManager>().Where(it => it.SalesOrganizationId == orgId).ToList();
        }

        public static List<SalesMan> getSalesManByOrgId(int orgId)
        {
            return SqlSugarHelp.scope.Queryable<SalesMan>()
                                        .Where(it => it.SalesManOrgID == orgId)
                                         .Mapper(it => it.City,
                                    it => it.SalesManCityID,
                                    it => it.City.cityid)
                                         .ToList();
        }

        public static int addManager(OrganizationManager organizationManager)
        {
            return SqlSugarHelp.scope.Insertable(organizationManager).ExecuteCommand();
        }
    }
}
